package com.zy.explore.generator.infrastructure.core.util;

import com.google.common.collect.Lists;
import com.zy.explore.generator.infrastructure.constant.ProjectConstant;
import com.zy.explore.generator.infrastructure.core.frm.BrickInfoFrm;
import com.zy.explore.generator.infrastructure.core.frm.DbManageFrm;
import com.zy.explore.generator.infrastructure.core.vo.TableColumnVO;
import com.zy.explore.generator.infrastructure.core.vo.TableVO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * description 数据库用户名密码校验工具类
 *
 * @author 🇨🇳中国印🇨🇳
 * @date 2021年04月10日 00:13
 **/
@Slf4j
public class DbHelper {

    /**
     * 方法说明：查询表信息
     *
     * @param frm 入参
     * @return {@link TableVO }
     */
    public static TableVO getTableInfo(DbManageFrm frm) throws Exception {
        Connection conn = null;
        try {
            // 1、装载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2、连接数据库
            String mysqlUrl = String.format(ProjectConstant.MYSQL_DB_URL, frm.getMysqlHost(), frm.getMysqlPort(),
                    frm.getMysqlDb(), frm.getMysqlUser(), frm.getMysqlPwd());
            conn = DriverManager.getConnection(mysqlUrl);
            if (conn != null) {
                // 3、创建连接语句
                String sql = "SELECT table_schema tableSchema, table_name tableName, ENGINE, table_collation tableCollation, table_comment tableComment, create_time createTime " +
                        "FROM information_schema.TABLES " +
                        "WHERE table_schema = '%s' AND table_name = '%s' " +
                        "ORDER BY create_time " +
                        "DESC LIMIT 1";
                sql = String.format(sql, frm.getMysqlDb(), frm.getTableName());
                ResultSet listResult;
                // 4.创建预编译对象
                PreparedStatement ps = conn.prepareStatement(sql);
                // 执行查询
                listResult = ps.executeQuery();
                //5、循环获得数据库字段生成对象
                List<TableVO> list = Lists.newArrayList();
                while (listResult.next()) {
                    list.add(TableVO.builder()
                            .tableSchema(listResult.getString("tableSchema"))
                            .tableName(listResult.getString("tableName"))
                            .engine(listResult.getString("engine"))
                            .tableCollation(listResult.getString("tableCollation"))
                            .tableComment(listResult.getString("tableComment"))
                            .createTime(listResult.getTimestamp("createTime"))
                            .build());
                }
                if (CollectionUtils.isEmpty(list)) {
                    return null;
                }
                return list.get(0);
            }
        } catch (ClassNotFoundException | SQLException e) {
            log.error("查询数据库表异常：", e);
            throw new Exception(e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }


    /**
     * 根据数据库表信息查询该表的列信息
     *
     * @param frm 数据库表配置信息
     * @return 返回列信息
     */
    public static List<TableColumnVO> getColumnInfo(BrickInfoFrm frm) {
        List<TableColumnVO> retList = new ArrayList<>();
        String mysqlUrl = String.format(ProjectConstant.MYSQL_DB_URL, frm.getMysqlHost(), frm.getMysqlPort(),
                frm.getMysqlDb(), frm.getMysqlUser(), frm.getMysqlPwd());
        String sql = "select column_name columnName, data_type dataType, column_comment columnComment, column_key columnKey, extra from information_schema.columns where table_name = ? and table_schema = (select database()) order by ordinal_position";
        try {
            // 1、装载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 设置超时时间，默认30s
            DriverManager.setLoginTimeout(1);
            Connection conn = DriverManager.getConnection(mysqlUrl);
            if (conn != null) {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1, frm.getTableName());
                ResultSet resultSet = ps.executeQuery();
                while (resultSet.next()) {
                    String columnName = resultSet.getString("columnName").toLowerCase();
                    String dataType = resultSet.getString("dataType");
                    String columnKey = resultSet.getString("columnKey");
                    String javaType = StrUtil.convertDataType(dataType);
                    TableColumnVO vo = TableColumnVO.builder()
                            .columnName(columnName)
                            .propName(StrUtil.underlineToHump(columnName))
                            .firstUpperPropName(StrUtil.underlineToHumpFirstUpperCase(columnName))
                            .dataType(dataType)
                            .jdbcType(StrUtil.convertToJdbcType(dataType))
                            .propType(javaType)
                            .columnComment(resultSet.getString("columnComment"))
                            .columnKey(columnKey)
                            .isPk(Objects.equals(columnKey, "PRI"))
                            .extra(resultSet.getString("extra"))
                            .build();
                    retList.add(vo);
                    if (Objects.equals(columnKey, "PRI")) {
                        frm.setPkType(javaType);
                        frm.setPkColumnName(columnName);
                        frm.setPkPropName(StrUtil.underlineToHump(columnName));
                        frm.setPkPropNameFirstUpper(StrUtil.underlineToHumpFirstUpperCase(columnName));
                    }
                }
                conn.close();
            }
        } catch (ClassNotFoundException | SQLException e) {
            log.error("连接数据库异常，无法查到表的列信息：", e);
        }
        return retList;
    }
}
